package userInteraction;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

@SuppressWarnings("serial")
public class readUserInformation implements Serializable{
	public ArrayList<String> readUserByEmail(String email){
		ArrayList<String> information = new ArrayList<String>();
		Connection con = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/licenseDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select Customer.firstName, Customer.lastName, Customer.city, Customer.phoneNumber,Logins.password from Customer join Logins on Customer.customerId=Logins.customerId where Customer.emailAddress=?");
			query.setString(1, email);
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				information.add(rs.getString("Customer.firstName"));
				information.add(rs.getString("Customer.lastName"));
				information.add(rs.getString("Customer.city"));
				information.add(rs.getString("Customer.phoneNumber"));
				information.add(rs.getString("Logins.password"));
			}
		}
		catch(Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {	
			}
		}
		if (information.size()==5){
			return information;
		}
		else return null;
	}
	
	public Integer readUserId(String email){
		int customerId = 0;
		Connection con = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/licenseDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select Customer.customerId from Customer where Customer.emailAddress=?");
			query.setString(1, email);
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				customerId = rs.getInt(1);
			}
		}
		catch(Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {	
			}
		}
		return customerId;
	}
	
	public String readUserNameById(Integer id){
		String fullName = "";
		Connection con = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/licenseDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select concat(firstName, ' ', lastName) from Customer where customerId = ?");
			query.setInt(1, id);
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				fullName = rs.getString(1);
			}
		}
		catch(Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {	
			}
		}
		return fullName;
	}

	public static void main(String args[]){
		readUserInformation x = new readUserInformation();
//		System.out.println(x.readUserId("kt4lyn@yahoo.com"));
		System.out.println(x.readUserNameById(2));
	}
}
